package com.tjtt.tdm.system.dao.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;

import com.tjtt.common.utils.StringUtil;
import com.tjtt.tdm.base.BaseHibernateDao;
import com.tjtt.tdm.base.PageResult;
import com.tjtt.tdm.system.bean.TdmMessage;
import com.tjtt.tdm.system.bean.TdmMessageUser;
import com.tjtt.tdm.system.dao.IMessageDao;

/**
 * @author Administrator
 *
 */
public class MessageDaoImpl extends BaseHibernateDao implements IMessageDao{

	@Override
	public void deleteMessage(String ids) {
		String[] idss = ids.split(",");
		for(String id : idss){
			TdmMessage message = (TdmMessage)this.getHibernateTemplate().get(TdmMessage.class, Long.parseLong(id));
			this.getHibernateTemplate().delete(message);
		}
	}

	@Override
	public TdmMessage getOneMessage(String id) {
		TdmMessage message = (TdmMessage)this.getHibernateTemplate().get(TdmMessage.class, Long.parseLong(id));
		return message;
	}
//  原实现方法
//	@Override
//	public PageResult queryMessage(Map filterMap) {
//		StringBuffer queryString = new StringBuffer();
//		StringBuffer queryCountString = new StringBuffer();
//		
//		queryCountString.append("select count(*) "+
//								" FROM TDM_MESSAGE_USER T"+
//								" LEFT JOIN TDM_MESSAGE M"+
//								" ON M.ID = T.TM_MESSAGE_ID"+
//								" LEFT JOIN SYS_USERS U"+
//								" ON U.USERID = M.TM_USER_ID where 1=1 ");
//		queryString.append("SELECT M.ID,"+
//								" M.TM_NOTICE_TITLE,"+
//								" U.USERNAME,"+
//								" M.TM_INPUT_TIME,"+
//								" M.TM_STATUS,"+
//								" M.TM_NOTICE_TYPE,"+
//								" T.TM_STATE"+
//								" FROM TDM_MESSAGE_USER T"+
//								" LEFT JOIN TDM_MESSAGE M"+
//								" ON M.ID = T.TM_MESSAGE_ID"+
//								" LEFT JOIN SYS_USERS U"+
//								" ON U.USERID = M.TM_USER_ID where 1=1 ");
//		if (filterMap!= null && !filterMap.isEmpty()) {
//			String filterProperty;
//			String filterValue;
//			//type 1：为申请人，2：试件负责人，3：试验室负责人，4：质量主管，5：结构主管，6：技术负责人，7：基层单位领导
//			//type 8：承试单位领导，9：主管军代表
//			filterProperty = "tm_notice_type";
//			filterValue = (String) filterMap.get(filterProperty);
//			if (!StringUtil.isMissing(filterValue)) {
//				queryString.append(" and m.tm_notice_type").append(" =").append(filterValue);
//				queryCountString.append(" and m.tm_notice_type").append(" =").append(filterValue);
//			} 
//			filterProperty = "messageName";
//			filterValue = (String) filterMap.get(filterProperty);
//			if (!StringUtil.isMissing(filterValue)) {
//				queryString.append(" and m.tm_notice_title").append(" like '%").append(StringUtil.encodingUrl(filterValue)).append("%'");
//				queryCountString.append(" and m.tm_notice_title").append(" like '%").append(StringUtil.encodingUrl(filterValue)).append("%'");
//			} 
//			filterProperty = "isread";
//			filterValue = (String) filterMap.get(filterProperty);
//			if (!StringUtil.isMissing(filterValue)) {
//				queryString.append(" and t.TM_STATE").append(" =").append(filterValue);
//				queryCountString.append(" and t.TM_STATE").append(" =").append(filterValue);
//			} 
//			filterProperty = "tm_user_id";
//			filterValue = (String) filterMap.get(filterProperty);
////			if (!StringUtil.isMissing(filterValue)) {
////				queryString.append(" and  t.TM_USER_ID").append(" =").append(filterValue);
////				queryCountString.append(" and  t.TM_USER_ID").append(" =").append(filterValue);
////			} 
//			filterProperty = "isreceive";
//			filterValue = (String) filterMap.get(filterProperty);
//			if (!StringUtil.isMissing(filterValue)) {
//				filterProperty = "tm_user_id";
//				String userid = (String) filterMap.get(filterProperty);
//				if (!StringUtil.isMissing(filterValue)) {
//					if("0".equals(filterValue)){//接收
//						queryString.append(" and  t.TM_USER_ID").append(" =").append(userid);
//						queryCountString.append(" and  t.TM_USER_ID").append(" =").append(userid);
//					}else if("1".equals(filterValue)){//发送
//						queryString.append(" and  m.TM_USER_ID").append(" =").append(userid);
//						queryCountString.append(" and  m.TM_USER_ID").append(" =").append(userid);
//					}
//				}
//			}
//			String sidx = (String) filterMap.get("sidx");
//			String sord = (String) filterMap.get("sord");
//			queryString.append(" order by " + sidx + " " + sord + " ");
//		}
//		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
//	}

	
	
	@Override
	public PageResult queryMessage(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		
		
		if (filterMap!= null && !filterMap.isEmpty()) {
			String filterProperty;
			String filterValue;
			
			//修改后
			String isread = "isread";
			String isreadValue = (String) filterMap.get(isread);
			
			filterProperty = "isreceive";
			filterValue = (String) filterMap.get(filterProperty);
			if (!StringUtil.isMissing(filterValue)) {
				filterProperty = "tm_user_id";
				String userid = (String) filterMap.get(filterProperty);
				if (!StringUtil.isMissing(filterValue)) {
					if("0".equals(filterValue)){//接收
						queryCountString.append("select count(*) "+
								" FROM TDM_MESSAGE_USER T"+
								" LEFT JOIN TDM_MESSAGE M"+
								" ON M.ID = T.TM_MESSAGE_ID"+
								" LEFT JOIN SYS_USERS U"+
								" ON U.USERID = M.TM_USER_ID where 1=1 ");
						queryString.append("SELECT M.ID,"+
								" M.TM_NOTICE_TITLE,"+
								" U.USERNAME,"+
								" M.TM_INPUT_TIME,"+
								" M.TM_STATUS,"+
								" M.TM_NOTICE_TYPE,"+
								" T.TM_STATE"+
								" FROM TDM_MESSAGE_USER T"+
								" LEFT JOIN TDM_MESSAGE M"+
								" ON M.ID = T.TM_MESSAGE_ID"+
								" LEFT JOIN SYS_USERS U"+
								" ON U.USERID = M.TM_USER_ID where 1=1 ");
		
						filterProperty = "tm_notice_type";
						filterValue = (String) filterMap.get(filterProperty);
						if (!StringUtil.isMissing(filterValue)) {
							queryString.append(" and m.tm_notice_type").append(" =").append(filterValue);
							queryCountString.append(" and m.tm_notice_type").append(" =").append(filterValue);
						} 
						filterProperty = "messageName";
						filterValue = (String) filterMap.get(filterProperty);
						if (!StringUtil.isMissing(filterValue)) {
							queryString.append(" and m.tm_notice_title").append(" like '%").append(StringUtil.encodingUrl(filterValue)).append("%'");
							queryCountString.append(" and m.tm_notice_title").append(" like '%").append(StringUtil.encodingUrl(filterValue)).append("%'");
						} 
						
						queryString.append(" and  t.TM_USER_ID").append(" =").append(userid);
						queryCountString.append(" and  t.TM_USER_ID").append(" =").append(userid);
						if(!StringUtils.isEmpty(isreadValue)){
							queryString.append(" and t.TM_STATE").append(" =").append(isreadValue);
							queryCountString.append(" and t.TM_STATE").append(" =").append(isreadValue);
						}
					}else if("1".equals(filterValue)){//发送，sql语句不同，若相同，并且一个消息发给多个人，则可能会查出多条相同记录
						queryCountString.append("select count(*) "+
								" FROM TDM_MESSAGE M"+
								" LEFT JOIN SYS_USERS U"+
								" ON U.USERID = M.TM_USER_ID where 1=1 ");
						queryString.append("SELECT M.ID,"+
								" M.TM_NOTICE_TITLE,"+
								" U.USERNAME,"+
								" M.TM_INPUT_TIME,"+
								" M.TM_STATUS,"+
								" M.TM_NOTICE_TYPE,"+
								" M.TM_DEPTS"+  //字段没意义，为了对齐viewArray的ttm_state字段
								" FROM TDM_MESSAGE M"+
								" LEFT JOIN SYS_USERS U"+
								" ON U.USERID = M.TM_USER_ID where 1=1 ");
		
						filterProperty = "tm_notice_type";
						filterValue = (String) filterMap.get(filterProperty);
						if (!StringUtil.isMissing(filterValue)) {
							queryString.append(" and m.tm_notice_type").append(" =").append(filterValue);
							queryCountString.append(" and m.tm_notice_type").append(" =").append(filterValue);
						} 
						filterProperty = "messageName";
						filterValue = (String) filterMap.get(filterProperty);
						if (!StringUtil.isMissing(filterValue)) {
							queryString.append(" and m.tm_notice_title").append(" like '%").append(StringUtil.encodingUrl(filterValue)).append("%'");
							queryCountString.append(" and m.tm_notice_title").append(" like '%").append(StringUtil.encodingUrl(filterValue)).append("%'");
						} 
						
						queryString.append(" and  m.TM_USER_ID").append(" =").append(userid);
						queryCountString.append(" and  m.TM_USER_ID").append(" =").append(userid);
						
						queryString.append(" and m.TM_STATUS").append(" =").append("1");
						queryCountString.append(" and m.TM_STATUS").append(" =").append("1");
					}
				}
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
			System.out.println(queryString.toString());
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	
	
	
	
	@Override
	public PageResult queryGonggao(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		
		queryCountString.append("select count(*) "+
							 " FROM TDM_MESSAGE T"+
							  " LEFT JOIN SYS_USERS U"+
							  " ON U.USERID = T.TM_USER_ID");
		queryString.append("SELECT T.ID, T.TM_USER_ID, T.TM_NOTICE_TITLE,"+
						      " U.USERNAME,"+
						      " T.TM_INPUT_TIME,"+
						      " T.TM_STATUS,"+
						      " T.TM_NOTICE_TYPE"+
							  " FROM TDM_MESSAGE T"+
							  " LEFT JOIN SYS_USERS U"+
							  " ON U.USERID = T.TM_USER_ID where 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			String filterProperty;
			String filterValue;
			//type 1：为申请人，2：试件负责人，3：试验室负责人，4：质量主管，5：结构主管，6：技术负责人，7：基层单位领导
			//type 8：承试单位领导，9：主管军代表
			filterProperty = "tm_notice_type";
			filterValue = (String) filterMap.get(filterProperty);
			if (!StringUtil.isMissing(filterValue)) {
				queryString.append(" and t.tm_notice_type").append(" =").append(filterValue);
				queryCountString.append(" and t.tm_notice_type").append(" =").append(filterValue);
			} 
			if(null != filterMap.get("messageName") &&! "".equals(filterMap.get("messageName"))){
				queryString.append(" and T.TM_NOTICE_TITLE like '%" +  filterMap.get("messageName") + "%' ");
				queryCountString.append(" and T.TM_NOTICE_TITLE like '%" +  filterMap.get("messageName") + "%' ");
			}
			if(null != filterMap.get("messageType") &&! "".equals(filterMap.get("messageType"))){
				queryString.append(" and T.TM_NOTICE_TYPE like '%" +  filterMap.get("messageType") + "%' ");
				queryCountString.append(" and T.TM_NOTICE_TYPE like '%" +  filterMap.get("messageType") + "%' ");
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	@Override
	public String saveMessage(TdmMessage message) {
		String restr = this.getHibernateTemplate().save(message).toString();
		if(message.getTmNoticeType() != null && "1".equals(message.getTmNoticeType())){//如果类型是个人信息
			String userids = message.getTmUsersid();
			if(!StringUtils.isEmpty(userids)){
				String[] ids = userids.split(",");
				for(String id : ids){
					TdmMessageUser user = new  TdmMessageUser();
					user.setTmMessageId(Long.parseLong(restr));
					user.setTmUserId(Long.parseLong(id));
					user.setTmState(0l);
					this.getHibernateTemplate().save(user);
				}
			}
		}
		return restr;
	}

	@Override
	public String updateMessage(TdmMessage message) {
		this.getHibernateTemplate().update(message);
		String type = message.getTmNoticeType();
		Query query = getSession().createSQLQuery("delete from tdm_message_user t where t.tm_message_id=" + message.getId());
		query.executeUpdate();
		if(type != null && "1".equals(type)){
			String userids = message.getTmUsersid();
			if(!StringUtils.isEmpty(userids)){
				String[] ids = userids.split(",");
				for(String id : ids){
					TdmMessageUser user = new  TdmMessageUser();
					user.setTmMessageId(message.getId());
					user.setTmUserId(Long.parseLong(id));
					user.setTmState(0l);
					this.getHibernateTemplate().save(user);
				}
			}
		}
		return null;
	}

	@Override
	public String updateMessageUser(String userid, String msgid) {
		List<TdmMessageUser> list = (List<TdmMessageUser>)this.getHibernateTemplate()
									.find("from TdmMessageUser t where t.tmMessageId="+Long.parseLong(msgid)+" and t.tmUserId="+ Long.parseLong(userid));
		if(list != null && list.size()>0){
			TdmMessageUser user = list.get(0);
			user.setTmState(1l);
			this.getHibernateTemplate().update(user);
		}
		return null;
	}

	public int receiveMessageCount(String userid) {
		List tmu= this.getHibernateTemplate().find("from TdmMessageUser t where t.tmState = 0 and  t.tmUserId="+ Long.parseLong(userid));
		if(tmu!= null && tmu.size()>0){
			return tmu.size();
		}
		return 0;
	}

	public int toDoThingCount(String userid,String role) {
		String sql = "SELECT t.ID FROM TDM_INCREMENT_LIST T LEFT JOIN tdm_commission c ON t.commission_id = c.Id " +
				"WHERE (',' || T.RECEIVE_ID || ',' LIKE '%,"+ userid +",%' or T.RECEIVE_ROLE_ID IN ("+role+")) AND T.INCREMENT_STATUS = 0 AND c.tc_apply_state != '0' ";
		Query query = this.getSession().createSQLQuery(sql);
		List list = query.list();
		//List til = this.getHibernateTemplate().find("from TdmIncrementList i where i.incrementStatus = 0 and  i.receiveId  '" + userid + "' ");
		if(list!= null && list.size()>0){
			return list.size();
		}
		return 0;
	}

	//首页显示站内信息
	public List findMessage(Map filterMap) {
		String sql = "SELECT * FROM (SELECT M.ID, M.TM_NOTICE_TITLE,U.USERNAME,M.TM_INPUT_TIME FROM TDM_MESSAGE_USER T " +
								"LEFT JOIN TDM_MESSAGE M"+
								" ON M.ID = T.TM_MESSAGE_ID"+
								" LEFT JOIN SYS_USERS U"+
								" ON U.USERID = M.TM_USER_ID where 1=1 AND t.tm_state = 0 ";
		if (filterMap!= null && !filterMap.isEmpty()) {
			String filterProperty;
			String filterValue;
			
			filterProperty = "types";
			filterValue = (String) filterMap.get(filterProperty);
			if (!StringUtil.isMissing(filterValue)) {
				sql += " and M.tm_notice_type = "+filterValue;
			} 
			
			filterProperty = "userid";
			filterValue = (String) filterMap.get(filterProperty);
			if (!StringUtil.isMissing(filterValue)) {
				sql += " and t.TM_USER_ID = "+filterValue;
			}
		}
		sql += " order by id desc ) where rownum <= 6";
		SQLQuery sqlquery = this.getSession().createSQLQuery(sql);
		List messageList = sqlquery.list();
		return messageList;
	}

	//首页系统公告
	public List findGonggao(Map filterMap) {
		String sql = "SELECT * FROM (SELECT  t.ID, t.TM_NOTICE_TITLE,U.USERNAME,t.TM_INPUT_TIME "+
						  " FROM TDM_MESSAGE T"+
						  " LEFT JOIN SYS_USERS U"+
						  " ON U.USERID = T.TM_USER_ID where 1=1 ";
		if (filterMap!= null && !filterMap.isEmpty()) {
			String filterProperty;
			String filterValue;
			
			filterProperty = "types";
			filterValue = (String) filterMap.get(filterProperty);
			if (!StringUtil.isMissing(filterValue)) {
				sql += " and t.tm_notice_type = "+filterValue;
			} 
		}
		sql += " order by id desc ) where rownum <= 6";
		SQLQuery sqlquery = this.getSession().createSQLQuery(sql);
		List gonggao = sqlquery.list();
		return gonggao;
	}
	
	@Override
	public List findDaiBanList(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		queryString.append("SELECT * FROM (SELECT T.ID,T.CODE_ID,T.COMMISSION_ID, C.TC_TEST_NAME,M.MODULE_NAME,TO_CHAR(C.TC_TEST_PROMOTERS_DATE,'yyyy-MM-dd hh24:mi:ss')," +
				"TO_CHAR(C.TC_START_DATE,'yyyy-MM-dd'),TO_CHAR(C.TC_END_DATE,'yyyy-MM-dd'), M.MODULE_TABLE,M.MODULE_HANDLE, c.TC_PAGE_TYPE," +
				"c.TC_TEST_IS_MAJOR,t.OPERATION_MOUDLE_ID,c.TC_TEST_NATURE,t.TEST_PROJECT_NAME,c.TC_TEST_NATURE_NAME,c.TC_TEST_TYPE_NAME, T.OPERATION_TIME,T.IS_DEAL" +
				" FROM TDM_INCREMENT_LIST T " +
				" LEFT JOIN SYS_USERS u ON u.USERID||''=t.RECEIVE_ID" +
				" LEFT JOIN DICT_MODULE m ON m.MODULE_ID = t.OPERATION_MOUDLE_ID||''" +
				" LEFT JOIN TDM_COMMISSION c ON c.ID=t.COMMISSION_ID" +
				" WHERE 1=1 and c.TC_APPLY_STATE != '0' ");
				if (filterMap!= null && !filterMap.isEmpty()) {
				String filterProperty;
				String filterValue;
				String status = String.valueOf(filterMap.get("status"));
				if("0".equals(status)){
					filterProperty = "userId";
					filterValue = (String) filterMap.get(filterProperty);
					//这里查询把数据库和要查询的字符全转成小写
					if (!StringUtil.isMissing(filterValue)) {
						queryString.append(" and (','||T.RECEIVE_ID||',' LIKE '%,").append(filterValue).append(",%'");
					}
					filterProperty = "role";
					filterValue = (String) filterMap.get(filterProperty);
					//这里查询把数据库和要查询的字符全转成小写
					if (!StringUtil.isMissing(filterValue)) {
						queryString.append(" or T.RECEIVE_ROLE_ID in(").append(filterValue).append("))");
					}
				}else if("1".equals(status)){
					filterProperty = "userId";
					filterValue = (String) filterMap.get(filterProperty);
					//这里查询把数据库和要查询的字符全转成小写
					if (!StringUtil.isMissing(filterValue)) {
						queryString.append(" and T.OPERATION_ID=").append(filterValue);
					}
				}
				filterProperty = "status";
				filterValue = (String) filterMap.get(filterProperty);
				//这里查询把数据库和要查询的字符全转成小写
				if (!StringUtil.isMissing(filterValue)) {
					queryString.append(" and T.INCREMENT_STATUS=").append(filterValue);
				}
			}
				queryString.append("order by id desc ) where rownum <= 6");
		SQLQuery sqlquery = this.getSession().createSQLQuery(queryString.toString());
		List dbList = sqlquery.list();
		return dbList;
	}

	@Override
	public int getUnreadMessageCount(String userid) {
		// TODO Auto-generated method stub
		StringBuffer queryCountString = new StringBuffer();
		
		queryCountString.append("select count(*) "+
				" FROM TDM_MESSAGE_USER T"+
				" LEFT JOIN TDM_MESSAGE M"+
				" ON M.ID = T.TM_MESSAGE_ID where 1=1 ");

		queryCountString.append(" and m.tm_notice_type").append(" =").append("1");//站内消息，非公告
		
		queryCountString.append(" and  t.TM_USER_ID").append(" =").append(userid);//当前用户
		
		queryCountString.append(" and t.TM_STATE").append(" =").append("0");//未读状态
//		System.out.println(queryCountString.toString());
		Query query = this.getSession().createSQLQuery(queryCountString.toString());
		List<Number> count = query.list();
		int total = Integer.parseInt(count.get(0).toString());
//		System.out.println(total);
		return total;
	}

	@Override
	public int getSheBeiCount(String orgid,String userId) {
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*) from rquipment_manage t "+
								" where (to_char(to_date(t.rquipment_next_time,'yyyy-MM-dd' )- t.Rquipment_Warn_Day, "+
                                " 'yyyy-MM-dd') <= (select to_char(sysdate, 'yyyy-MM-dd') from dual))");
		if(!"5286".equals(userId)){
			queryCountString.append(" and t.SYBM_ID ").append(" = '").append(orgid+"'");//当前部门id
		}
		Query query = this.getSession().createSQLQuery(queryCountString.toString());
		List<Number> count = query.list();
		int total1 = Integer.parseInt(count.get(0).toString());
		return total1;
	}
	
	@Override
	public List getCerifyName() {
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select distinct u.username "+
			  "from sys_cerify t left join SYS_USERS u on t.cerify_user = u.USERID "+
			  "where u.del_flag=1 and (to_char(t.CERIFY_PAST_TIME, 'yyyy-MM-dd') <= "+
			  "(select to_char(sysdate, 'yyyy-MM-dd') from dual))");
		Query query = this.getSession().createSQLQuery(queryCountString.toString());
		List list= query.list();
		return list;
	}
}
